"""
Case Type   : Castfunc
Case Name   : 测试cast对binary_double类型进行强制转换
Create At   : 2022-02-23
Owner       : wang_zhengyuan
Description :
    1、设置参数enable_copy_server_files参数值为on
    2、gsql连接数据库，创建表
    3、使用copy命令插入数据
    4、查询cast对binary_double类型进行强制转换
    5、环境清理
Expect      :
    1、参数值修改成功
    2、gsql连接数据库，创建表成功
    3、数据插入成功
    4、查询成功
    5、清理成功
History     :
"""

import os
import unittest
import subprocess

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import macro


class Opengauss_Function_Ustore_DQL_Castfunc_Case0001(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'------ {os.path.basename(__file__)} start------')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = CommonSH('PrimaryRoot')
        self.com = Common()
        self.constant = Constant()
        self.t_name1 = 't_Ustore_DQL_Castfunc_Case_type_000'
        self.t_name2 = 't_Ustore_DQL_Castfunc_Case_base_000'
        self.copy_file1 = 'vecctor_type_tab'
        self.copy_file2 = 'vecctor_base_tab'
        self.cur_path_1 = os.path.join(macro.SCRIPTS_PATH, self.copy_file1)
        self.cur_path_2 = os.path.join(macro.SCRIPTS_PATH, self.copy_file2)
        self.target_path = macro.DB_INSTANCE_PATH
        self.tar_path_1 = os.path.join(macro.DB_INSTANCE_PATH, self.copy_file1)
        self.tar_path_2 = os.path.join(macro.DB_INSTANCE_PATH, self.copy_file2)

    def test_main(self):
        step_txt = '----step1:修改参数值，expect: 参数值修改成功----'
        self.log.info(step_txt)
        self.log.info('---------------获取并打印参数初始值------------------')
        self.show_copy_pram = f'show enable_copy_server_files;'
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.pram_init = cmd_result.strip().splitlines()[-2].strip()

        self.log.info('---------------开始修改参数值------------------')
        self.pri_sh.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                  "enable_copy_server_files=on")

        self.log.info('---------------检查参数是否修改成功------------------')
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.cmd_result_value1 = cmd_result.strip().splitlines()[-2].strip()
        self.assertEqual('on', self.cmd_result_value1, "参数修改失败")

        step_txt = '----step2:创建表，expect: 创建成功----'
        self.log.info(step_txt)
        create_sql = f"drop table if exists {self.t_name1};\
                        create table {self.t_name1} (\
                          col_tinyint       tinyint,\
                          col_smallint      smallint,\
                          col_int        integer,\
                          col_bigint       bigint,\
                          col_money            varchar(1000),\
                          col_numeric       numeric,\
                          col_real       real,\
                          col_double       double precision ,\
                          col_decimal          decimal,\
                          col_varchar        varchar(1000),\
                          col_char       char(30),\
                          col_nvarchar2      nvarchar2(1000),\
                          col_text       varchar(1000),\
                          col_timestamp   varchar(1000),\
                          col_timestamptz   timestamp without time zone,\
                          col_date       date,\
                          col_time       varchar(1000),\
                          col_timetz       varchar(1000),\
                          col_interval      varchar(1000),\
                          col_tinterval        varchar(1000),\
                          col_smalldatetine  varchar(1000),\
                          col_bytea    bytea,\
                          col_boolean    boolean,\
                          col_inet    varchar(1000),\
                          col_cidr    varchar(1000),\
                          col_bit     varchar(100),\
                          col_varbit    varchar(1000),\
                          col_oid     varchar(1000)\
                        ) with (storage_type=ustore);" \
            f"drop table if exists {self.t_name2};\
                create table {self.t_name2} (\
                  col_tinyint       tinyint,\
                  col_smallint      smallint,\
                  col_int        integer,\
                  col_bigint       bigint,\
                  col_numeric       numeric,\
                  col_real       real,\
                  col_double       double precision,\
                  col_decimal          decimal,\
                  col_varchar        varchar(1000),\
                  col_char       char(30),\
                  col_nvarchar2      nvarchar2(1000),\
                  col_text       varchar(1000),\
                  col_timestamptz   varchar(1000),\
                  col_timestamp   timestamp without time zone,\
                  col_date       date,\
                  col_time       varchar(1000),\
                  col_timetz       varchar(1000),\
                  col_interval      varchar(1000),\
                  col_smalldatetine  date\
                )\
                with (storage_type=ustore) partition by range (col_int)\
                (\
                partition vector_base_tab_000_1 values less than (10),\
                partition vector_base_tab_000_2 values less than (77),\
                partition vector_base_tab_000_3 values less than (337),\
                partition vector_base_tab_000_4 values less than (573),\
                partition vector_base_tab_000_5 values less than (1357),\
                partition vector_base_tab_000_6 values less than (2033),\
                partition vector_base_tab_000_7 values less than (2087),\
                partition vector_base_tab_000_8 values less than (2387),\
                partition vector_base_tab_000_9 values less than (2687),\
                partition vector_base_tab_000_10 values less than (2987),\
                partition vector_base_tab_000_11 values less than (maxvalue)\
                ); "
        self.log.info(create_sql)
        create_result = self.pri_sh.execut_db_sql(create_sql)
        self.log.info(create_result)
        self.assertTrue(create_result.count(self.constant.TABLE_CREATE_SUCCESS)
                        == 2, '执行失败:' + step_txt)

        step_txt = '----step3:使用copy命令插入数据，expect: 数据插入成功----'
        self.log.info(step_txt)
        text = '----step3.1: 获取当前主机的IP地址 expect: 成功----'
        self.log.info(text)
        cmd = "ifconfig -a|grep inet6 -a2|grep broadcast|awk '{print $2}'"
        self.log.info(cmd)
        self.cur_ip = subprocess.getoutput(cmd).strip()
        self.log.info(self.cur_ip)

        text = '----step3.2: 复制文件到数据库主机对应目录 expect: 成功----'
        self.log.info(text)
        if self.cur_ip == self.pri_sh.node.ssh_host:
            self.log.info('当前主机是数据库主机，无需远程复制')
            cmd = f"cp {self.cur_path_1} {self.target_path};" \
                f"cp {self.cur_path_2} {self.target_path}"
            self.log.info(cmd)
            res = self.pri_sh.node.sh(cmd).result()
            self.log.info(res)
        else:
            self.com.scp_file(self.pri_sh.node, self.copy_file1,
                              self.target_path)
            self.com.scp_file(self.pri_sh.node, self.copy_file2,
                              self.target_path)

        text = '----step3.3: 查看数据库主机目标路径下文件 expect: 成功----'
        self.log.info(text)
        cmd = f"ls -l {self.target_path}|" \
            f"grep -E '{self.copy_file1}|{self.copy_file2}'"
        self.log.info(cmd)
        res = self.pri_sh.node.sh(cmd).result()
        self.log.info(res)
        self.assertNotIn('No such file or directory', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file1}' and f'{self.copy_file2}', res,
                      '文件拷贝失败')

        text = '----step3.4:使用copy命令插入数据，expect: 数据插入成功----'
        self.log.info(text)
        self.log.info('---------------使用copy命令插入数据------------------')
        copy_sql = f"copy {self.t_name1} from '{self.tar_path_1}' " \
            f"delimiters ',';" \
            f"copy {self.t_name2} from '{self.tar_path_2}'  delimiters ',';"
        self.log.info(copy_sql)
        copy_result = self.pri_sh.execut_db_sql(copy_sql)
        self.log.info(copy_result)
        self.assertEqual(copy_result.strip().splitlines()[0].strip(),
                         'COPY 3621', '执行失败:' + step_txt)
        self.assertEqual(copy_result.strip().splitlines()[1].strip(),
                         'COPY 3437', '执行失败:' + step_txt)

        step_txt = '----step4: 查询cast对binary_double类型进行强制转换,expect: 查询成功----'
        self.log.info(step_txt)
        select_sql = f"select count(*) from (" \
            f"select cast(a.col_real as varchar(100)),\
            cast(b.col_real as varchar(100)) + cast(a.col_real as numeric),\
            cast(b.col_real as decimal), cast(a.col_real as int),\
            cast(a.col_real as int), cast(a.col_real as float),\
            cast(b.col_real * 0.00001 as int), a.col_real\
            from {self.t_name1} a join {self.t_name2} b\
            on coalesce(cast(a.col_real as varchar(100)), 1 ::varchar(100)) =\
            coalesce(cast(b.col_real as varchar(100)), '1')\
            where a.col_smallint < any( array[-3,-7,3] )\
            order by 1, 2, 3, 4, 5, 6 , 7 , 8 );"
        self.log.info(select_sql)
        select_result = self.pri_sh.execut_db_sql(select_sql)
        self.log.info(select_result)
        tmp_count1 = select_result.strip().splitlines()[-2].strip()
        self.assertEqual(tmp_count1, '27840', '执行失败：' + step_txt)

        self.log.info(step_txt)
        select_sql = f" select cast(a.col_real as varchar(100)),\
          cast(b.col_real as varchar(100)) + cast(a.col_real as numeric),\
          cast(b.col_real as decimal),cast(a.col_real as int),\
          cast(a.col_real as int),cast(a.col_real as float),\
          cast(b.col_real * 0.00001 as int),a.col_real\
          from {self.t_name1} a join {self.t_name2} b\
          on coalesce(cast(a.col_real as varchar(100)), 1 ::varchar(100)) =\
          coalesce(cast(b.col_real as varchar(100)), '1')\
         where a.col_smallint > any( array[-3,-7,3] )\
         order by 1, 2, 3, 4, 5, 6 , 7 , 8 limit 50;"
        self.log.info(select_sql)
        select_result = self.pri_sh.execut_db_sql(select_sql)
        self.log.info(select_result)
        tmp_value = select_result.strip().splitlines()[-1]
        self.assertIn('50 rows', tmp_value, '执行失败：' + step_txt)

    def tearDown(self):
        self.log.info('----this is teardown----')
        step_txt = '----step5: 环境清理，expect:清理成功----'
        self.log.info(step_txt)
        self.log.info('---------------因后续用例依赖暂不删表------------------')
        drop_sql = f'drop table if exists {self.t_name1},{self.t_name2} ;'
        self.log.info(drop_sql)

        rm_cmd = f"rm -rf {self.tar_path_1} {self.tar_path_2}"
        rm_res = self.pri_sh.node.sh(rm_cmd).result()
        self.log.info(rm_res)

        self.log.info('---------------开始还原参数值------------------')
        self.pri_sh.execute_gsguc("reload", self.constant.GSGUC_SUCCESS_MSG,
                                f"enable_copy_server_files={self.pram_init}")

        self.log.info('---------------检查参数是否还原成功------------------')
        self.log.info(self.show_copy_pram)
        cmd_result = self.pri_sh.execut_db_sql(self.show_copy_pram)
        self.log.info(cmd_result)
        self.cmd_result_value2 = cmd_result.strip().splitlines()[-2].strip()
        self.assertEqual(rm_res, '', '文件删除失败' + step_txt)
        self.assertEqual(self.pram_init, self.cmd_result_value2, "参数还原失败")
        self.log.info(f'----- {os.path.basename(__file__)} end-----')
